2016-01-27 - 24454.100 - Enhancement - Consolidated epm reports #CrystalReportsSupport
ENHANCEMENT
24454.100 - Consolidated EPM reports
Requirement Summary
Need to create Consolidated EPM reports for Customer, Category, License & Silhouette.
Admin Info
Purpose
|
Need to create Consolidated EPM reports for Customer, Category, License & Silhouette.
|
Requested By
|
Abe Maritime
|
Spec Created By
|
Dhananjay G
|
Spec Created Date
|
01/27/2016
|
Spec QA by
|
Uday Kumar P
|
Objects
|
Consolidated EPM reports for Customer, Category, License & Silhouette.
|
Document Status
|
Complete
|
Estimates
SNo
|
Activity
|
Estimation in Hours
|
1
|
Research
|
04
|
2
|
Documentation
|
04
|
3
|
Development/ Config
|
170
|
4
|
Unit test in DEV
|
04
|
5
|
Unit test in QUA
|
04
|
6
|
Other activity(transports) / Validation
|
56
|
|
TOTAL
|
242
|
References
Prior Tickets
None
Documents
Consolidated EPM notes.pdf
Spec Changes
SNo
|
Change Details
|
Requested By
|
Requested On
|
Notes if any
|
1
|
YTD Budget or MTD Budget data is blank validation
|
Saravanan
|
01/28/2016
|
Email
|
2
|
1) Remove the page break / white space between page1 and page2
2) In MTD it is showing open + shipped. Please make sure it is showing only shipped
3) Please add the quantity section back from my original report.
4) When we add the quantities, there might be less space. Hence, use the formula similar to this for all measures divide by 1000:
(Sum ({ZMP_OPASA_ZMP_OPASA_Q001.[Measures]-[00O2THVXVZKSOIG14MGMSSBER]}, {@Group2}) / 1000)
5) Please make sure the fields are aligned properly.
|
Saravanan
|
01/29/2016
|
Email
|
3
|
Add the blank budget data fields to 'others'
|
Saravanan
|
01/29/2016
|
Email
|
4
|
1) All the coverages are in negatives. Please check the formula
2) YTD and MTD should be same for Jan (period 1) and validation with BW BEx query
|
Saravanan
|
02/01/2016
|
Email
|
5
|
Add MTD Qty measures to BEx Query ZBPC_MP_Q021
|
Saravanan
|
02/04/2016
|
Email
|
6
|
Modify contribution percentages from Contribution Margin to Gross Margin
|
Saravanan
|
02/11/2016
|
Email
|
7
|
Currency Conversion for Consolidated EPM Reports.
|
Saravanan
|
02/15/2016
|
Email
|
8
|
Create new parameter for choose Contribution % between Contribution and Gross.
|
Saravanan
|
02/24/2016
|
IM
|
9
|
Create one more new parameter for Product Type.
|
Saravanan
|
02/25/2016
|
IM
|
10
|
Add product type fourth option 'ALL' is default and did u check whether it is possible to show product type only when north america is chosen
or else suppress it (if any other region is chosen)
|
Saravanan
|
02/26/2016
|
IM
|
11
|
'Others' logic implemented in all Consolidated EPM reports and validation with BEx query.
|
Saravanan
|
03/02/2016
|
Email
|
Functional Requirement
Purpose/WHY:
Email from Saravanan on 01/27/2016:
I have attached a new report framework and the notes for what the report should do. Please go through it and I will call you tomorrow.
We already have the framework. I need both of us to work simultaneously and this needs to be done as quickly as possible due to the requirements' nature.
Email from Saravanan on 02/04/2016:
Naveen,
As we talked about few days back, I would want you to start the BEx development to add the MTD information in the quantity field.
Dhananjay,
1) Change all the MTD description to MTH
2) Adjust the size of the columns and fields to accommodate YTD and MTH field labels in the quantity field.
Email from Saravanan on 02/11/2016:
Dhananjay,
We are going to make a little change to the report that I have attached. Right now, I believe the contribution is showing Margin right now and we would like to change it to gross margin (ONLY COST and no manual - cogs).This will not be a permanent change as of now but the Executives want to see the data.
So,
Naveen, please update the query to have gross margin field for ytd, mtd and budget. Let me know once it is done. We can move it to production with a cm.
Dhananjay, please prepare accordingly to update he margin to gross margin in the report.
Email from Saravanan on 02/15/2016:
I didn't get to work on it. However SA160 seems to do the same. Just take a look, if you need to know how it's done.
IM from Saravanan on 02/24/2016:
report with gross margin developed only customer scorecard, inside the crystal designer..we would see a question mark on teh left saying parameters.
we should use that option after the report is run to choose between contribution and gross.
IM from Saravanan on 02/25/2016:
we have to add one more parameter/prompt called Product Type. The product type should have three options:
i) US Headwear --> only sales org 1000 and not apparel)
ii) US Apparel --> Sales org 1000 only apparel + sales org 5000
iii) Canada HW --> Sales Org 1200 (and canada is chosen then everything should be in canadian dollars)
IM from Saravanan on 02/26/2016:
Add product type fourth option 'ALL' is default and did u check whether it is possible to show product type only when north america is chosen or else suppress it (if any other region is chosen)
Email from Saravanan on 03/02/2016:
As of now, I am working on figuring the reason for it. If I find anything substantial, then I will drop an email. If not, then please do look at this first. Then focus on "others" formula and product type formula.
In Scope:
Consolidated EPM reports need to be created for Customer, Category, License & Silhouette.
Out of Scope:
None
Solution Summary
BW Changes:
1. Need to create a new BEx Query same as the copy of ZBPC_MP_Q001.
2. Need to add new key figures to the Query as specified in the requirement by NEC.
3. Need to collect the new BEx into the transport request.
4. Need to validate the Bex Query with the crystal report.
02/05/2016
1. Need to add Qty MTD measures to BEx Query ZBPC_MP_Q021.
2. Need to collect all the BEx Query changes into transport request.
Crystal Changes:
1. Need to modify the following changes in Consolidated EPM report for Customer:
i. Need to develop the Consolidated EPM reports for North America region only.
ii. Need to sort the Customer name and Actual revenue shipped Current Year date by Descending order and 'Others' setup at the end of the report.
iii. Need to display all customers which has BPC budget data.
iv. Need to create the formulas for
MTD section same as
YTD section.
v. Need to set 'LY Var' all the values rounded to nearest 1000 in
YTD section.
vi. Need to replace 'FY' by 'MTD' and also modify the corresponding formulas in
Contribution % section.
vii. Need to remove Quantity section completely and also format the whole report accordingly.
2. Need to modify the following changes in Consolidated EPM report for Customer:
i. Need to remove the page break / white space between page1 and page2.
ii. Need to modify in MTD it is showing open + shipped. Please make sure it is showing only shipped.
iii. Need to add the quantity section back from original EPM200 report.
iv. Need to use the formula similar to this for all measures divide by 1000:
(Sum ({ZMP_OPASA_ZMP_OPASA_Q001.[Measures]-[00O2THVXVZKSOIG14MGMSSBER]}, {@Group2}) / 1000)
v. Need to add the blank budget data fields to 'others' in the report.
vi. Need to check the formulas all the coverage's are in negatives.
3. Need to Modify contribution percentages from Contribution Margin to Gross Margin in the report.
4. Need to implement Currency Conversions for Consolidated EPM Reports.
5. Need to create new parameters for selecting Contribution % between Contribution and Gross.
6. Need to add one more parameter/prompt called Product Type. The product type should have three options:
i. US Headwear - Only sales org 1000 and not apparel.
ii. US Apparel - Sales org 1000 only apparel + sales org 5000.
iii. Canada HW - Sales Org 1200 (and canada is chosen then everything should be in Canadian dollars).
iv. ALL (default).
7. Need to check the possibility to show product type only when North America is chosen or else suppress it (if any other region is chosen).
8. Need to implement 'Others' logic when budget is zero or blank and '#' values in all Consolidated EPM reports.
Test Plan
Test ID
|
Test Scenario
|
Expected Results
|
1
|
Report Format.
|
Should be as per NEC crystal standard checklist.
|
2
|
Execute Crystal Report in Crystal Designer tool after changes.
|
Report should execute successfully in Crystal Designer tool.
|
3
|
Schedule the Report in NEC BI Launch Pad.
|
Report should execute successfully in NEC BI Launch Pad.
|
Solution Details
BW Changes:
1. Need to create a new BEx Query ZBPC_MP_Q021 same as the copy of ZBPC_MP_Q001.
2. Need to add new MTD key figures to the BEx Query ZBPC_MP_Q021 as specified in the requirement by NEC.
3. Need to collect the new BEx into the transport request.
02/05/2016
1. Need to add Shipped Quantity previous Month, Total Open Qty Current Month, Budget sales Qty Current month to ZBPC_MP_Q021 BEx Query.
2. Need to collect all the BEx Query changes into the transport request.
Crystal Changes:
1. Need to modify the following changes in Consolidated EPM report for Customer:
i. Need to develop the Consolidated EPM reports for North America region only.
ii. Need to sort the Customer name and Actual revenue shipped Current Year date by Descending order and 'Others' setup at the end of the report.
iii. Need to create the formulas for MTD section same as YTD section.
iv. Need to set 'LY Var' all the values rounded to nearest 1000 in
YTD section.
v. Need to replace 'FY' by 'MTD' and also modify the corresponding formulas in
Contribution % section.
vi. Need to remove Quantity section completely and also format the whole report accordingly.
2. Need to modify the following changes in Consolidated EPM report for Customer:
i. Need to remove the page break / white space between page1 and page2.
ii. Need to modify in MTD it is showing open + shipped. Please make sure it is showing only shipped.
iii. Need to add the quantity section back from original EPM200 report.
iv. Need to use the formula similar to this for all measures divide by 1000:
(Sum ({ZMP_OPASA_ZMP_OPASA_Q001.[Measures]-[00O2THVXVZKSOIG14MGMSSBER]}, {@Group2}) / 1000)
v. Need to add the blank budget data fields to 'others' in the report.
vi. Need to check the formulas why all the coverage's are in negatives.
3. Need to update contribution percentage formulas from Contribution Margin to Gross Margin in the report.
4. Need to implement Currency Conversions for Consolidated EPM Reports.
5. Need to create new parameter for choosing Contribution % between Contribution and Gross.
6. Need to validate the all consolidated reports with BW BEx query i.e ZBPC_MP_Q021 and OLAP query.
7. Need to add one more parameter/prompt called Product Type. The product type should have three options:
i. US Headwear - Only sales org 1000 and not apparel.
ii. US Apparel - Sales org 1000 only apparel + sales org 5000.
iii. Canada HW - Sales Org 1200 (and canada is chosen then everything should be in Canadian dollars).
iv. ALL (default).
8. Need to check the possibility to show product type only when North America is chosen or else suppress it (if any other region is chosen).
9. Need to implement 'Others' logic in all Consolidated EPM reports.
Issues
None